﻿using HtmlAgilityPack;
using MySql.Data.MySqlClient;
using NLog;
using OpenQA.Selenium;
using OpenQA.Selenium.Chrome;
using System.Collections.ObjectModel;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Forms;
using System.Windows.Input;
public static class StringExtensions
{
    public static bool IsUrlIsValid(this string uriName)
    {
        Uri uriResult;
        bool result = Uri.TryCreate(uriName, UriKind.Absolute, out uriResult)
            && (uriResult.Scheme == Uri.UriSchemeHttp || uriResult.Scheme == Uri.UriSchemeHttps);
        return result;
    }
}

namespace 建设项目数据爬取
{

    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        private CancellationTokenSource cts = new CancellationTokenSource();
        private static string LogFilePath = "D:\\log_file.txt";
        AddNewCity addCityWindow;
        private static string jinhuaBaseUrl = "http://sthjj.jinhua.gov.cn";
        private static string jinhuaUrl = "http://sthjj.jinhua.gov.cn/col/col1229168520/index.html";
        private static string dongyangBaseUrl = "https://www.dongyang.gov.cn";
        private static string dongyangUrl = "https://www.dongyang.gov.cn/col/col1229326206/index.html";
        private static string wuyiBaseUrl = "http://www.zjwy.gov.cn";
        private static string wuyiUrl = "http://www.zjwy.gov.cn/col/col1229450807/index.html";
        private  string wuhuUrl = "https://sthjj.wuhu.gov.cn/content/column/6789182?pageIndex=";
        private static string wuhuBaseUrl = "https://sthjj.wuhu.gov.cn";
        private static string lanxiURL = "http://www.lanxi.gov.cn/col/col1229288169/index.html";
        private static string lanxiBaseURL = "http://www.lanxi.gov.cn";

        private static string jinyunURL = "https://www.jinyun.gov.cn/col/col1229425888/index.html";
        private static string jinyunBaseURL = "https://www.jinyun.gov.cn";

        private static string yongkangURL = "https://www.yk.gov.cn/col/col1229191548/index.html";
        private static string yongkangBaseURL = "https://www.yk.gov.cn";

        private static string pujiangURL = "https://www.pj.gov.cn/col/col1229430227/index.html?uid=8279827&pageNum=1";
        private static string pujiangBaseURL = "https://www.pj.gov.cn";


        private static string taizhouURL = "https://sthjj.zjtz.gov.cn/col/col1229214289/index.html";
        private static string taizhouBaseURL = "https://sthjj.zjtz.gov.cn";

        private static string hangzhouURL2025 = "https://epb.hangzhou.gov.cn/col/col1229849621/index.html";
        private static string hangzhouURL2024 = "https://epb.hangzhou.gov.cn/col/col1229779339/index.html";
        private static string hangzhouURL2023 = "https://epb.hangzhou.gov.cn/col/col1229710857/index.html";
        private static string hangzhouURL2022 = "https://epb.hangzhou.gov.cn/col/col1229615238/index.html";
        private static string hangzhouURL2021 = "https://epb.hangzhou.gov.cn/col/col1229472271/index.html";
        private static string hangzhouURL2020 = "https://epb.hangzhou.gov.cn/col/col1228991807/index.html";
        private static string hangzhouBaseURL = "https://epb.hangzhou.gov.cn";
        private static string shaoxingURL = "https://sxepb.sx.gov.cn/col/col1229459599/index.html";
        private static string shaoxingBaseURL = "https://sxepb.sx.gov.cn";

        private static string zhejiangURL = "https://sthjt.zj.gov.cn/col/col1201950/index.html";
        private static string zhejiangBaseURL = "https://sthjt.zj.gov.cn";

        private static string wenzhouURL = "https://sthjj.wenzhou.gov.cn/col/col1317633/index.html";
        private static string wenzhouBaseURL = "https://sthjj.wenzhou.gov.cn";

        private static string ningboURL = "https://jyh.nbepb.gov.cn/CPA/Jsxm.html?typeid=1&classid=0";
        private static string ningboBaseURL = "http://sthjj.ningbo.gov.cn";

        private static string huainanURL = "https://sthjj.huainan.gov.cn/content/column/22859263?pageIndex=";
        private static string huainanBaseURL = "https://sthjj.huainan.gov.cn";

        private static string xuanchengURL = "https://sthjj.xuancheng.gov.cn/News/showList/43279/page_";
        private static string xuanchengBaseURL = "https://sthjj.xuancheng.gov.cn";

        private static string bengbuURL = "https://sthjj.bengbu.gov.cn/content/column/6814211?pageIndex=";
        private static string bengbuBaseURL = "https://sthjj.bengbu.gov.cn";

        private static string lishuiURL = "http://hb.lishui.gov.cn/col/col1229248152/index.html?uid=6244633&pageNum=";
        private static string lishuiBaseURL = "http://hb.lishui.gov.cn";

        private static string hefeiURL = "https://sthjj.hefei.gov.cn/site/tpl/13847?m=HPSL&pageIndex=";
        private static string hefeiBaseURL = "https://sthjj.hefei.gov.cn";

        private HtmlNode titleNode, publishTimeNode;
        private HtmlNodeCollection indexPageNodeCollection;
        private HtmlDocument document, detailPageDoc;
        public int pageNum = 1;
        ObservableCollection<Xpaths> xpaths;
        Xpaths obj;
        private static string BaseURL; 
        public MainWindow()
        {
            InitializeComponent();
            DatabaseTool.CreateTables();
        }

        /// <summary>
        /// 获取首页节点集合的公用方法
        /// </summary>
        /// <param name="indexUrl">爬虫初始页面URL</param>
        /// <param name="itemXpath">初始页面待爬取item的xpath</param>
        /// <returns></returns>
        HtmlNodeCollection? getIndexPageNodeCollection(string indexUrl, string itemXpath, int sleepTime)
        {
            document = new HtmlDocument();
            document = SimulateWebBroswer(indexUrl, sleepTime);
            if (document != null)
                return document.DocumentNode.SelectNodes(itemXpath);
            else
                return null;
        }

        /// <summary>
        /// 获取详情页标题和发布时间
        /// </summary>
        /// <param name="detailPageNode">详情页节点</param>
        /// <param name="cityBaseURL">城市主域名</param>
        /// <param name="detailTitleXpath">详情页标题xpath</param>
        /// <param name="publishTimeXpath">发布时间xpath</param>
        void getDetailTitleAndPublishTime(HtmlNode detailPageNode, string cityBaseURL, string detailTitleXpath, string publishTimeXpath)
        {
            // 解析获取详情页URL
            string href = detailPageNode.Attributes["href"].Value;
            href = href[..4] == "http" ? href : cityBaseURL + href;

            // 访问并解析该市的详情页
            HtmlWeb web = new HtmlWeb();
            web.OverrideEncoding = Encoding.GetEncoding("utf-8");
            detailPageDoc = web.Load(href);
            HtmlNode titleNode = detailPageDoc.DocumentNode.SelectSingleNode("//head");
            //string headStr = titleNode.InnerText;
            //if (headStr.Contains("404") | headStr.Contains("Not Found"))
            //{
                
            //}
            // 解析并获取该市详情页标题
            titleNode = detailPageDoc.DocumentNode.SelectSingleNode(detailTitleXpath);

            // 解析并获取发布时间
            HtmlNodeCollection collection = detailPageDoc.DocumentNode.SelectNodes(publishTimeXpath);
            if(collection != null)
            {
                foreach (HtmlNode node in collection)
                {
                    string innerText = node.InnerText;
                    if (innerText.Contains("日期") |
                        innerText.Contains("发布") |
                        innerText.Contains("时间") |
                        innerText.Contains("年") |
                        innerText.Contains("月") |
                        innerText.Contains("日"))
                    {
                        publishTimeNode = node;
                        continue;
                    }
                }
            }
        }

        /// <summary>
        /// // 解析获取环评文件链接并存入数据库
        /// </summary>
        /// <param name="detailDoc">详情页html</param>
        /// <param name="cityName">城市名称</param>
        void attachmentURLParseAndSaveToCity(HtmlDocument detailDoc, string cityBaseUrl, string cityName)
        {
            // 解析并获取环评文件链接
            HtmlNodeCollection fileNodes = detailDoc.DocumentNode.SelectNodes("//a");

            if (fileNodes != null) 
            {
                foreach (HtmlNode item in fileNodes)
                {
                    if (item != null && item.Attributes.Contains("href"))
                    {
                        string fileURL = item.Attributes["href"].Value;
                        if (fileURL == null)
                        {
                            continue;
                        }
                        if (item.Attributes.Contains("href") && fileURL.Length >= 4)
                        {
                            fileURL = fileURL[..4] == "http" ? fileURL : cityBaseUrl + fileURL;
                            fileURL = fileURL.Replace("&amp;", "&");

                            if (
                                fileURL.Contains(".pdf") |
                                fileURL.Contains(".doc") |
                                fileURL.Contains(".docx") |
                                fileURL.Contains("fileurl=") |
                                fileURL.Contains(".zip") |
                                fileURL.Contains(".rar") |
                                fileURL.Contains("download?")
                                )
                            {
                                Debug.WriteLine("此链接是环评文件" + fileURL);

                                string titleStr = titleNode == null ? "标题未解析到" : titleNode.InnerText;
                                titleStr = titleStr.Replace("\r", "");
                                titleStr = titleStr.Replace("\n", "");
                                titleStr = titleStr.Replace("\t", "");
                                titleStr = titleStr.Replace(" ", "");
                                titleStr = titleStr.Replace("&nbsp;", "");

                                string pubTimeStr = publishTimeNode == null ? "null" : publishTimeNode.InnerText;
                                pubTimeStr = pubTimeStr.Replace("\r", "");
                                pubTimeStr = pubTimeStr.Replace("\n", "");
                                pubTimeStr = pubTimeStr.Replace("\t", "");
                                pubTimeStr = pubTimeStr.Replace(" ", "");

                                string attachmentNameStr = item == null ? "null" : item.InnerText;
                                attachmentNameStr = attachmentNameStr.Replace("\r", "");
                                attachmentNameStr = attachmentNameStr.Replace("\n", "");
                                attachmentNameStr = attachmentNameStr.Replace("\t", "");
                                attachmentNameStr = attachmentNameStr.Replace(" ", "");

                                insertIntoMysql(cityName, titleStr, attachmentNameStr, fileURL, pubTimeStr);
                            }
                            else
                            {
                                Debug.WriteLine("此链接不是环评文件" + fileURL);
                            }
                        }
                        else
                        {
                            Debug.WriteLine("此标签没有href属性");
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 把数据插入 MySQL 数据库
        /// </summary>
        /// <param name="地区"></param>
        /// <param name="标题"></param>
        /// <param name="PDF链接"></param>
        /// <param name="公告时间"></param>
        void insertIntoMysql(string district, string detailTitle, string fileTitle, string fileLink, string publishTime)
        {
            DatabaseTool.GetInstance().OpenDatabase("xmjs");
            //将系统时间写给数据库
            string creatTime = DateTime.Now.ToLocalTime().ToString();
            string insertsql = string.Format("INSERT IGNORE INTO xmjstable(district,detailTitle,fileTitle,fileLink,publishTime,createTime) values('{0}','{1}','{2}','{3}','{4}','{5}')", district, detailTitle, fileTitle,fileLink,publishTime,creatTime);
            MySqlCommand command = new MySqlCommand(insertsql, DatabaseTool.connection);
            try
            {
                int count = command.ExecuteNonQuery();//执行数据库的非查询命令
                if (count > 0)//如果返回值（影响的行数）大于0，提示录入成功
                {
                    Debug.WriteLine("数据插入成功!");
                }
                DatabaseTool.GetInstance().CloseDatabase("xmjs");
            }
            catch (Exception ex)
            {
                Helper.Log(LogFilePath,ex); 
                throw;
            }
        }

        /// <summary>
        /// 获取下一页链接URL
        /// </summary>
        /// <returns>下一页的URL</returns>
        string GetNextPageURL()
        {
            string nextPageURL = "没找到下一页的按钮";
            if (document != null) 
            {
                HtmlNodeCollection allHrefsNode = document.DocumentNode.SelectNodes("//a");
                if (allHrefsNode != null)
                {
                    foreach (HtmlNode nextPageNode in allHrefsNode)
                    {
                        if (nextPageNode != null)
                        {
                            string nextBtnText = nextPageNode.InnerText;
                            string disabled = "";
                            if (nextPageNode.Attributes.Contains("disabled"))
                                if (disabled == "disabled")
                                    return "没找到下一页的按钮";

                            string nextBtnHeader = "";
                            if (nextPageNode.Attributes.Contains("title"))
                                nextBtnHeader = nextPageNode.Attributes["title"].Value;
                            string nextBtnStr = nextBtnHeader + nextBtnText;
                            if (nextBtnStr.Contains("下一页") | nextBtnStr.Contains("下页") | nextBtnStr.Contains("翻页"))
                            {
                                nextPageURL = nextPageNode.Attributes["href"].Value;
                                if (nextPageURL.Length > 4)
                                {
                                    string startUrl = nextPageURL[..4];
                                    if (startUrl != "http")
                                        nextPageURL = BaseURL + nextPageURL;
                                    nextPageURL = nextPageURL.Replace("&amp;", "&");
                                    return nextPageURL;
                                }
                                else
                                {
                                    return nextPageURL;
                                }
                            }
                        }
                    }
                }
            }
            else
                return nextPageURL;
            return nextPageURL;
        }


        /// <summary>
        /// 使用selenium模拟加载网页
        /// </summary>
        /// <param name="url">爬虫启动页地址</param>
        /// <param name="sleepTime">selenium模拟加载网页时间</param>
        /// <returns></returns>
        private static HtmlDocument SimulateWebBroswer(string url, int sleepTime)
        {
            if (url.IsUrlIsValid())
            {
                ChromeOptions option = new ChromeOptions();
                option.AddArgument("--no-sandbox");
                option.AddArgument("--disable-dev-shm-usage");
                option.AddArgument("--remote-debugging-port=9222");
                IWebDriver chromeDriver = new ChromeDriver(@"D://chromedriver.exe", option, TimeSpan.FromSeconds(9000));
                chromeDriver.Navigate().GoToUrl(url);

                Thread.Sleep(sleepTime);
                string htmlStr = chromeDriver.PageSource;
                chromeDriver.Quit();
                HtmlDocument indexDoc = new HtmlDocument();
                indexDoc.LoadHtml(htmlStr);
                return indexDoc;
            }
            else
                Helper.ShowMessage(string.Format("'{0}' 是一个格式错误的URL地址，请检查后再试",url), "URL格式错误");
                return null;
        }

        private async void XMJS(Xpaths paths, int sleepTime) 
        {
            try
            {
                await Task.Run(() =>
                {
                    int index = paths.IndexUrlStr.IndexOf("gov.cn");
                    BaseURL = paths.IndexUrlStr.Substring(0, index + 6);
                    indexPageNodeCollection = getIndexPageNodeCollection(paths.IndexUrlStr, paths.IndexXpathStr, sleepTime);
                    if (indexPageNodeCollection != null)
                    {
                        foreach (HtmlNode node in indexPageNodeCollection)
                        {
                            // 解析并获取建设项目详情页的标题和发布日期
                            string href = node.Attributes["href"].Value;
                            href = href[..4] == "http" ? href : BaseURL + href;
                            bool isURL = href.IsUrlIsValid();
                            if (!isURL)
                                continue;
                            getDetailTitleAndPublishTime(node, BaseURL, paths.DetailXpathStr, paths.PublishTimeXpathStr);
                            // 解析获取环评文件链接并存入数据库
                            attachmentURLParseAndSaveToCity(detailPageDoc, BaseURL, paths.CityNameStr);
                        }
                        // 翻到下一页
                        paths.IndexUrlStr = GetNextPageURL();
                        XMJS(paths, sleepTime);
                    }
                    else
                    {
                        Console.WriteLine("此页面没有解析到建设项目数据");
                    }
                });
            }
            catch (Exception ex)
            {
                File.AppendAllText(LogFilePath, DateTime.Now.ToString() + ex.Message);
                throw;
            }
        }

        void refreshData()
        {
            if (xpaths == null)
                xpaths = new ObservableCollection<Xpaths>();
            if(obj == null)
                obj = new Xpaths();

            obj.CityNameStr = addCityWindow.cityName;
            obj.IndexUrlStr = addCityWindow.url;
            obj.IndexXpathStr = addCityWindow.indexXpath;
            obj.DetailXpathStr = addCityWindow.detailTitleXpath;
            obj.PublishTimeXpathStr = addCityWindow.detailPublishTimeXpath;
            xpaths.Add(obj);
            dataGrid.ItemsSource = xpaths;
        }

        private void StartTask(object sendor, RoutedEventArgs e)
        {
            int sleepTime;
            try
            {
                sleepTime = int.Parse(sleepTimeTextbox.Text.Length == 0 ? "2500" : sleepTimeTextbox.Text);
            }
            catch (Exception ex)
            {
                File.AppendAllText(LogFilePath, DateTime.Now.ToString() + ex.Message);
                throw;
            }
            if(xpaths != null)
            {
                foreach (Xpaths item in xpaths)
                {
                    try
                    {
                        XMJS(item, sleepTime);
                    }
                    catch (Exception ex)
                    {
                        File.AppendAllText(LogFilePath, DateTime.Now.ToString() + ex.Message);
                        throw;
                    }
                }
            }
            else
            {
                MessageBox.Show("没有待爬取城市信息");
            }
        }

        private async void ClearDataGrid(object sendor, RoutedEventArgs e)
        {
            xpaths = null;
            dataGrid.ItemsSource = null;
            dataGrid.Items.Clear();
        }


        private void AddNewCity(object sender, RoutedEventArgs e)
        {
            addCityWindow = new AddNewCity();
            addCityWindow.XpathIsAdded += () => refreshData();
            addCityWindow.Show();
        }

        private void searchTextbox_KeyDown(object sender, System.Windows.Input.KeyEventArgs e)
        {
            if (e.Key == Key.Enter)
            {
                QueryCityName(QueryButton,e);
            }
        }

        

        private void  QueryCityName(object sendor, RoutedEventArgs eventArgs)
        {
            if (xpaths == null)
                xpaths = new ObservableCollection<Xpaths>();
            xpaths.Clear();

            DatabaseTool.GetInstance().OpenDatabase("xmjs");
            string querySQL = string.Format("SELECT * FROM Xpaths WHERE cityName like '%{0}%';",searchTextbox.Text);
            MySqlCommand command = new MySqlCommand(querySQL, DatabaseTool.connection);
            using(MySqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Xpaths paths = new Xpaths();
                    paths.CityNameStr = reader["cityName"].ToString();
                    paths.IndexUrlStr = reader["url"].ToString();
                    paths.IndexXpathStr = reader["indexXpath"].ToString();
                    paths.DetailXpathStr = reader["detailTitleXpath"].ToString();
                    paths.PublishTimeXpathStr = reader["publishTimeXpath"].ToString();
                    xpaths.Add(paths);
                }
                dataGrid.ItemsSource = xpaths;
                if(xpaths.Count == 0)
                {
                    Helper.ShowMessage("没有数据", "查询结果");
                }
            }
            DatabaseTool.GetInstance().CloseDatabase("xmjs");
        }

        private void DeleteSelectedRow(object sendor, RoutedEventArgs e)
        {
            if (dataGrid.SelectedItem != null)
            {
                
                Xpaths xpath = dataGrid.SelectedItem as Xpaths;
                
                DatabaseTool.GetInstance().OpenDatabase("xmjs");
                string DeleteSQL = string.Format("DELETE FROM xpaths WHERE cityName like '%{0}%' AND url LIKE '%{1}%';",xpath.CityNameStr, xpath.IndexUrlStr);
                DatabaseTool.GetInstance().DeleteFrom(DeleteSQL);
                string querySQL = "select * from xpaths;";
                MySqlCommand command = new MySqlCommand(querySQL, DatabaseTool.connection);
                xpaths.Clear();
                using (MySqlDataReader reader = command.ExecuteReader())
                {

                    while (reader.Read())
                    {
                        Xpaths path = new Xpaths();
                        path.CityNameStr = reader["cityName"].ToString();
                        path.IndexUrlStr = reader["url"].ToString();
                        path.IndexXpathStr = reader["indexXpath"].ToString();
                        path.DetailXpathStr = reader["detailTitleXpath"].ToString();
                        path.PublishTimeXpathStr = reader["publishTimeXpath"].ToString();
                        xpaths.Add(path);
                    }
                }
                DatabaseTool.GetInstance().CloseDatabase("xmjs");
            }
        }
        public class Xpaths
        {
            public string? CityNameStr { get; set; }
            public string? IndexUrlStr { get; set; }
            public string? IndexXpathStr { get; set; }
            public string? DetailXpathStr { get; set; }
            public string? PublishTimeXpathStr { get; set; }
        }
    }
}